from fillpat import *
from openpyxl.styles import NamedStyle, Font, Side, Border
from openpyxl import Workbook
from copy import deepcopy
import numpy as np
import pandas as pd

cell_coord = Coord(1, 3)
print('------ 检测 Coord 对象 -----')
print(cell_coord.row == 1)
print(cell_coord.col == 3)
print(cell_coord.colletter == 'C')

highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
cell = Excell(cell_coord, 'cell value')
print(cell.loc == cell_coord)
print(cell.value == 'cell value')
print(cell.style == highlight)

cell11 = deepcopy(cell)
cell11.loc.row = 1
cell11.loc.col = 1
cell11.value = '(1,1)'
# cell11st = deepcopy(highlight)
# cell11st.name = 'cell11st'

cell12 = deepcopy(cell)
cell12.loc.row = 1
cell12.loc.col = 2
cell12.value = '(1,2)'
# cell12st = deepcopy(highlight)
# cell12st.name = 'cell12st'


cell13 = deepcopy(cell)
cell13.loc.row = 1
cell13.loc.col = 3
cell13.value = '(1,3)'
# cell13st = deepcopy(highlight)
# cell13st.name = 'cell13st'

cell21 = deepcopy(cell)
cell21.loc.row = 2
cell21.loc.col = 1
cell21.value = '(2,1)'
# cell21st = deepcopy(highlight)
# cell21st.name = 'cell21st'

cell22 = deepcopy(cell)
cell22.loc.row = 2
cell22.loc.col = 2
cell22.value = '(2,2)'
# cell22st = deepcopy(highlight)
# cell22st.name = 'cell22st'

cell23 = deepcopy(cell)
cell23.loc.row = 2
cell23.loc.col = 3
cell23.value = '(2,3)'
# cell23st = deepcopy(highlight)
# cell23st.name = 'cell23st'

cell31 = deepcopy(cell)
cell31.loc.row = 3
cell31.loc.col = 1
cell31.value = '(3,1)'
# cell31st = deepcopy(highlight)
# cell31st.name = 'cell31st'

cell32 = deepcopy(cell)
cell32.loc.row = 3
cell32.loc.col = 2
cell32.value = '(3,2)'
# cell32st = deepcopy(highlight)
# cell32st.name = 'cell32st'

cell33 = deepcopy(cell)
cell33.loc.row = 3
cell33.loc.col = 3
cell33.value = '(3,3)'
# cell33st = deepcopy(highlight)
# cell33st.name = 'cell33st'

martrix = np.array([[cell11, cell12, cell13],
           [cell21, cell22, cell23],
           [cell31, cell32, cell33]])

merges = ['A2:B2', 'C2:C3']

heights = pd.Series([40, 40, 40], index=range(1,4))

widths = pd.Series([50, 50, 50], index=range(1,4))

block = Exblock(martrix, merges, heights, widths)

print('------ 检测 Exblock 对象 -----')
print(block.number_of_rows == 3)
print(block.number_of_cols == 3)
print(block.martrix[0,0].value == '(1,1)')
print(block.martrix[0,0].loc.row == 1)
print(block.martrix[0,0].loc.col == 1)
print(block.martrix[2,1].value == '(3,2)')

block.offset(4,2)
print(block.martrix[0,0].loc.row == 5)
print(block.martrix[0,0].loc.colletter == 'C')
print(block.martrix[0,0].value == '(1,1)')
print(block.merges[0] == 'C6:D6')
print(block.merges[1] == 'E6:E7')

print(list(block.heights.index) == [5,6,7])
print(list(block.widths.index) == [3,4,5])


sheet = Exsheet(pattern=block,absolute_loc=Coord(1,1),gap_between_pages=2)

sheet.read_pattern(r'.\datas\请款单模板.xlsx')
# print(sheet.block.martrix[0][0].loc.row)
# print(sheet.block.martrix[1][0].loc.row)
# print(sheet.block.martrix[2][0].loc.row)
# print(sheet.block.martrix[0][0].loc.col)
# print(sheet.block.martrix[1][0].loc.col)
# print(sheet.block.martrix[2][0].loc.col)
wb = Workbook()
ws = wb.active

# for row in sheet.pattern.martrix:
#     for cell in row:
#         wb.add_named_style(cell.style)

for i in range(1,3):
    sheet.Id = i

    oto_loc = {
        'M2':'填单日期',
        'E3':'收款单位',
        'M3':'请款部门',
        'E4':'银行账户',
        'M4':'开户银行',
        'F5':'大写金额',
        'M5':'小写金额',
        'B7':'用途',
        'O9':'附件'
    }
    data = pd.Series(
        {
            '填单日期':'2022-03-29',
            '收款单位':'罗毅',
            '请款部门':'业务部',
            '银行账户':'2022 0411 3349 7812 139',
            '开户银行':'建设银行云飞路支行',
            '大写金额':'贰拾壹万元整',
            '小写金额': 210000,
            '用途':'收废品',
            '附件': 8
        }
    )

    sheet.fill_blank(oto_loc,data)
    sheet.put_in_sheet(ws)

    wb.save('test.xlsx')
    print('done')




